﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DataHelper;
using Utils;
namespace DAL
{
    public class EmployeeDao
    {
        // 部门	工号	 姓名	属性 	职位	 区域	 性别	 出生日期	入职时间	是否缴纳社保	工资状态	标注	 属班组
        //[DeptName], [EmpCode], [EmpName], [DutyName], [AreaName], [StateName], [EmpSex], [EducationName], [Registry], [JoinTime], [SafetyAmount], [SalaryState], [Remark], [WorkGroup], [Mobile]
        //导出
        string empFields = XmlUtility.ReadFiled("Admin_Employee");//" [DeptName] as 部门, [EmpCode] as 工号, [EmpName] as 姓名, [StateName] as 属性,[DutyName] as 职位, [AreaName] as 区域,  [EmpSex] as 性别,convert(char(10),Birthday,120)  as 出生日期,convert(char(10),[JoinTime],120) as 入职时间, (case [SafetyAmount] WHEN 0 THEN null else cast([SafetyAmount] as decimal(6,0)) End)  as 是否缴纳社保, [SalaryState] as 工资状态, [Remark] as 标注, [WorkGroup] as 属班组 ";
        //显示
        string strFields = " [DeptName], [EmpCode], [EmpName], [DutyName], [AreaName], [StateName], [EmpSex], [EducationName], [Registry], [JoinTime],[LeaveTime], (case [SafetyAmount] WHEN 0 THEN null else cast([SafetyAmount] as decimal(6,0)) End) as SafetyAmount, [SalaryState], [Remark], [WorkGroup], [Mobile] ";
        string allFields = " * ";
        #region 公用方法
        public bool Update(string tableName, SortedDictionary<string, string> sdList)
        {
            return new BuiltSQL().UpdateTable(tableName, sdList);
        }
        public bool Insert(string tableName, SortedDictionary<string, string> sdList)
        {
            return new BuiltSQL().InsertTable(tableName, sdList);
        }
       
        #endregion


        #region 雇员信息
        /// <summary>
        /// 根据工号获取一个员工信息
        /// </summary>
        /// <param name="empCode"></param>
        /// <returns>DataTable</returns>
        public DataTable GetOneInfo(string empCode)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt("select * from View_Emp where EmpCode='" + empCode + "'");
            //必须智能有一行数据，表中更有主键约束
            return dt;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="empCode"></param>
        /// <param name="statu">N 离职 请长假的 Y 在职等其它类型</param> 
        /// <returns></returns>
        public DataTable GetOneInfo(string empCode, string statu)
        {
            if (string.IsNullOrWhiteSpace(statu)) statu = "Y";
            DataTable dt = new DataTable();
            if (statu == "Y")
                dt = SQLHelper.ExecuteSqlGetDrToDt("select * from View_Emp where StateID not in (3,4) and EmpCode='" + empCode + "'");
            else
                dt = SQLHelper.ExecuteSqlGetDrToDt("select * from View_Emp where StateID in (3,4) and EmpCode='" + empCode + "'");
            //必须智能有一行数据，表中更有主键约束
            return dt;
        }
        /// <summary>
        /// 根据姓名 获取一个员工的信息
        /// </summary>
        /// <param name="empName"></param>
        /// <returns></returns>
        public DataTable GetOneInfoByName(string empName)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt("select * from View_Emp where EmpName='" + empName.Trim() + "'");
            //必须智能有一行数据，表中更有主键约束
            return dt;
        }
        /// <summary>
        /// 验证工号是被使用
        /// </summary>
        /// <param name="empCode"></param>
        /// <returns>bool</returns>
        public bool IsExistEmpCode(string empCode)
        {
            DataTable dt = GetOneInfo(empCode);
            if (dt.Rows.Count == 1)//如果存在该用户编号
                return true;
            else//否则
                return false;
        }
        public bool IsExistEmpCode(string empCode, string statu)
        {
            DataTable dt = GetOneInfo(empCode, statu);
            if (dt.Rows.Count == 1)//如果存在该用户编号
                return true;
            else//否则
                return false;
        }
        /// <summary>
        /// 更改一个员工所在的部门
        /// </summary>
        /// <param name="oldDeptID"></param>
        /// <param name="newDeptID"></param>
        /// <returns></returns>
        public bool ChangeDept(string oldDeptID, string newDeptID)
        {
            string strSQL = "update Admin_Employee set deptid=" + newDeptID + " where deptid=" + oldDeptID;
            return SQLHelper.ExecuteSql(strSQL);
        }
        /// <summary>
        /// 更改一个员工的工号，这个不提倡使用，而且要慎重使用
        /// </summary>
        /// <param name="oldCode"></param>
        /// <param name="newCode"></param>
        /// <param name="codeType"></param>
        /// <returns></returns>
        public bool ModiEmpCode(string oldCode, string newCode, string codeType)
        {
            bool flag = true;
            try
            {
                SqlParameter oldC = new SqlParameter("@oldCode", SqlDbType.VarChar, 500);
                oldC.Value = oldCode;

                SqlParameter newC = new SqlParameter("@newCode", SqlDbType.VarChar, 1000);
                newC.Value = newCode;
                //检索信息的条件
                SqlParameter cType = new SqlParameter("@codeType", SqlDbType.VarChar, 500);
                cType.Value = codeType;

                SqlParameter[] spList = { oldC, newC, cType };
                SQLHelper.ExecutePorcedure("spJimModiCode", spList);
            }
            catch (Exception ee)
            {
                flag = false;
                LogHelper.WriteLog(ee.ToString());
            }

            return flag;

        }
        //更改雇员工资账号
        public bool ModiEmpBankAccount(string empCode, string bankName, string bankAccount)
        {
            string strSQL = "Update Admin_Employee set SalaryState='" + bankName + "',SalaryAccount='" + bankAccount + "' Where EmpCode='" + empCode + "';";
            strSQL += "Update Wage_EmpItem set BankName='" + bankName + "',BankAccount='" + bankAccount + "' Where EmpCode='" + empCode + "';";
            return SQLHelper.ExecuteSql(strSQL);
        }
        /// <summary>
        /// 获取员工列表 使用分页
        /// </summary>
        /// <param name="page"></param>
        /// <param name="RowsPerPage"></param>
        /// <param name="empCode"></param>
        /// <param name="bTime"></param>
        /// <param name="eTime"></param>
        /// <param name="deptID"></param>
        /// <param name="stateID"></param>
        /// <param name="allRows"></param>
        /// <returns></returns>
        //public DataTable GetLists(int page, int RowsPerPage, string empCode, string bTime, string eTime, string deptID, string stateID, string timeType, out int allRows)
        //{
        //    string strTables = " [View_Emp] ";
        //    // string strFields = " [DeptName], [EmpCode], [EmpName], [DutyName], [AreaName], [StateName], [EmpSex], [EducationName], [Registry], [JoinTime], [SafetyAmount], [SalaryState], [Remark], [WorkGroup], [Mobile]  ";
        //    StringBuilder strFilter = new StringBuilder("1 = 1");
        //    string strSort = " DeptID ASC, EmpCode ASC";

        //    if (!string.IsNullOrWhiteSpace(empCode))
        //        strFilter.Append("and (EmpCode like '" + empCode + "%' OR EmpName like '%" + empCode + "%')");
        //    else
        //    {
        //        if (!string.IsNullOrWhiteSpace(deptID) && deptID != "0")
        //            strFilter.Append("and DeptID=" + Int32.Parse(deptID));
        //        if (!string.IsNullOrWhiteSpace(stateID) && stateID != "0")
        //            strFilter.Append("and StateID=" + Int32.Parse(stateID));

        //        switch (timeType)
        //        {
        //            case "JoinTime":
        //                if (RegexRule.IsDate(bTime)) strFilter.Append("and JoinTime >='" + bTime + "'");
        //                if (RegexRule.IsDate(eTime)) strFilter.Append("and JoinTime <='" + eTime + "'");
        //                break;
        //            case "InputTime":
        //                if (RegexRule.IsDate(bTime)) strFilter.Append("and InputTime >='" + bTime + "'");
        //                if (RegexRule.IsDate(eTime)) strFilter.Append("and InputTime <='" + eTime + "'");
        //                break;
        //            case "LeaveTime":
        //                if (RegexRule.IsDate(bTime)) strFilter.Append("and LeaveTime >='" + bTime + "'");
        //                if (RegexRule.IsDate(eTime)) strFilter.Append("and LeaveTime <='" + eTime + "'");
        //                break;
        //            case "Birthday":
        //                if (RegexRule.IsDate(bTime)) strFilter.Append("and Birthday >='" + bTime + "'");
        //                if (RegexRule.IsDate(eTime)) strFilter.Append("and Birthday <='" + eTime + "'");
        //                break;
        //            case "TestEndTime":
        //                if (RegexRule.IsDate(bTime)) strFilter.Append("and TestEndTime >='" + bTime + "'");
        //                if (RegexRule.IsDate(eTime)) strFilter.Append("and TestEndTime <='" + eTime + "'");
        //                break;
        //            //

        //        }


        //    }

        //    return ExecProc.RunProcGetLists(page, RowsPerPage, strTables, strFields, strFilter.ToString(), strSort, out allRows);
        //    //return employeeDao.GetLists(page, RowsPerPage, strTables, strFields, strFilter.ToString(), strSort, out allRows);

        //}

        //根据部门 显示某部门下的所有员工
        public DataTable GetLists(string account, string empCode, string deptID, string para)
        {
            //获取职位组
            string filter = "";
            DataTable dutyDT = new DutyDao().GetGroupList(account);
            if (dutyDT.Rows.Count > 0)
            {
                filter = " OR DutyID in " + dutyDT.Rows[0]["DutyArray"].ToString();
            }
            StringBuilder strFilter = new StringBuilder("Select ");
            strFilter.Append("a.[DeptName], a.[EmpCode], a.[EmpName], a.[DutyName], a.[AreaName], a.[StateName], a.[EmpSex], a.[EducationName], a.[Registry], a.[JoinTime], (case a.[SafetyAmount] WHEN 0 THEN null else cast(a.[SafetyAmount] as decimal(6,0)) End) as SafetyAmount, a.[SalaryState], a.[Remark], a.[WorkGroup], a.[Mobile],a.[LeaveTime],b.IsProcedure,a.Addr ");
            strFilter.Append(" From [View_Emp] a left join Admin_Emp_Leave b on a.EmpCode=b.EmpCode Where 1=1 ");
            if (!string.IsNullOrWhiteSpace(empCode))
                strFilter.Append(" and (a.EmpCode like '" + empCode + "%' OR a.EmpName like '%" + empCode + "%') and (a.DeptID in (" + deptID + ") " + filter + ")");
            else
                strFilter.Append(" and ((a.StateID <> 3) OR (DATEDIFF(month, a.LeaveTime, GETDATE()) <= 1)) and (a.DeptID in (" + deptID + ") " + filter + ")");

            strFilter.Append(" Order by a.DeptName asc, a.EmpCode asc");
            return SQLHelper.ExecuteSqlGetDrToDt(strFilter.ToString());
        }
        /// <summary>
        /// 导出信息 对应上一个方法
        /// </summary>
        /// <param name="account"></param>
        /// <param name="empCode"></param>
        /// <param name="deptID"></param>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetOutLists(string account, string empCode, string deptID, string para)
        {
            //获取职位组
            string filter = "";
            DataTable dutyDT = new DutyDao().GetGroupList(account);
            if (dutyDT.Rows.Count > 0)
            {
                filter = " OR DutyID in " + dutyDT.Rows[0]["DutyArray"].ToString();
            }
            StringBuilder strFilter = new StringBuilder("Select ");
            strFilter.Append("a.[DeptName] as '部门名称', a.[EmpCode] as '工号', a.[EmpName] as '姓名', a.[EmpSex] as '性别',a.[DutyName] as '职位', a.[AreaName] as '区域', a.[StateName] as '状态',  a.[EducationName] as '学历', a.[Registry] as '籍贯', a.[JoinTime] as '入职时间', (case a.[SafetyAmount] WHEN 0 THEN null else cast(a.[SafetyAmount] as decimal(6,0)) End) as '保险', a.[SalaryState] as '工资状态', a.[Remark] as '标注', a.[WorkGroup] as '班组', a.[Mobile] as '手机',b.IsProcedure as '是否交接',a.Addr as '地址',a.[LeaveTime] as '离职时间'");
            strFilter.Append(" From [View_Emp] a left join Admin_Emp_Leave b on a.EmpCode=b.EmpCode Where 1=1 ");
            if (!string.IsNullOrWhiteSpace(empCode))
                strFilter.Append(" and (a.EmpCode like '" + empCode + "%' OR a.EmpName like '%" + empCode + "%') and (a.DeptID in (" + deptID + ") " + filter + ")");
            else
                strFilter.Append(" and ((a.StateID <> 3) OR (DATEDIFF(month, a.LeaveTime, GETDATE()) <= 1)) and (a.DeptID in (" + deptID + ") " + filter + ")");

            strFilter.Append(" Order by a.DeptName asc, a.EmpCode asc");
            return SQLHelper.ExecuteSqlGetDrToDt(strFilter.ToString());
        }

        //员工查询界面
        public DataTable GetLists(string empCode, string bTime, string eTime, string deptID, string stateID, string timeType)
        {
            StringBuilder strFilter = new StringBuilder("Select ");
            strFilter.Append(strFields);
            strFilter.Append(" From [View_Emp] Where 1=1 ");
            if (!string.IsNullOrWhiteSpace(empCode)) strFilter.Append("and (EmpCode like '" + empCode + "%' OR EmpName like '%" + empCode + "%')");
            else
            {
                if (!string.IsNullOrWhiteSpace(deptID) && deptID != "0") strFilter.Append("and DeptID=" + Int32.Parse(deptID));
                if (!string.IsNullOrWhiteSpace(stateID) && stateID != "0")
                {
                    if (stateID == "10000")
                        strFilter.Append("and StateID<>3");//非离职人员
                    else
                        strFilter.Append("and StateID=" + Int32.Parse(stateID));
                }


                switch (timeType)
                {
                    case "JoinTime":
                        if (RegexRule.IsDate(bTime)) strFilter.Append("and JoinTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) strFilter.Append("and JoinTime <='" + eTime + "'");
                        break;
                    case "InputTime":
                        if (RegexRule.IsDate(bTime)) strFilter.Append("and InputTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) strFilter.Append("and InputTime <='" + eTime + "'");
                        break;
                    case "LeaveTime":
                        if (RegexRule.IsDate(bTime)) strFilter.Append("and LeaveTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) strFilter.Append("and LeaveTime <='" + eTime + "'");
                        break;
                    case "Birthday":
                        if (RegexRule.IsDate(bTime)) strFilter.Append("and Birthday >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) strFilter.Append("and Birthday <='" + eTime + "'");
                        break;
                    case "TestEndTime":
                        if (RegexRule.IsDate(bTime)) strFilter.Append("and TestEndTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) strFilter.Append("and TestEndTime <='" + eTime + "'");
                        break;

                }
            }
            strFilter.Append(" Order by DeptName asc, EmpCode asc");
            return SQLHelper.ExecuteSqlGetDrToDt(strFilter.ToString());
        }
        //员工查询界面的导出信息 仅供导出使用 区别在于导出字段和显示字段不同
        public DataTable GetOutLists(string empCode, string bTime, string eTime, string deptIDArray, string stateID, string timeType)
        {
            StringBuilder strFilter = new StringBuilder("Select ");
            strFilter.Append(empFields + ",ZipCode as 身份证 ");
            strFilter.Append(" From [View_Emp] Where 1=1 ");
            if (!string.IsNullOrWhiteSpace(empCode)) strFilter.Append("and (EmpCode like '" + empCode + "%' OR EmpName like '%" + empCode + "%')");
            else
            {
                if (!string.IsNullOrWhiteSpace(deptIDArray) && deptIDArray != "0")
                {
                    strFilter.Append("and DeptID in (" + deptIDArray + ")");
                }
               // if (!string.IsNullOrWhiteSpace(stateID) && stateID != "0")
                //    strFilter.Append("and StateID=" + Int32.Parse(stateID));
               // //else
               // //     strFilter.Append("and StateID<>3");//非离职人员
                if (!string.IsNullOrWhiteSpace(stateID) && stateID != "0")
                {
                    if (stateID == "10000")
                        strFilter.Append("and StateID<>3");//非离职人员
                    else
                        strFilter.Append("and StateID=" + Int32.Parse(stateID));
                }
                switch (timeType)
                {
                    case "JoinTime":
                        if (RegexRule.IsDate(bTime)) strFilter.Append("and JoinTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) strFilter.Append("and JoinTime <='" + eTime + "'");
                        break;
                    case "InputTime":
                        if (RegexRule.IsDate(bTime)) strFilter.Append("and InputTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) strFilter.Append("and InputTime <='" + eTime + "'");
                        break;
                    case "LeaveTime":
                        if (RegexRule.IsDate(bTime)) strFilter.Append("and LeaveTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) strFilter.Append("and LeaveTime <='" + eTime + "'");
                        break;
                    case "Birthday":
                        if (RegexRule.IsDate(bTime)) strFilter.Append("and Birthday >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) strFilter.Append("and Birthday <='" + eTime + "'");
                        break;
                    case "TestEndTime":
                        if (RegexRule.IsDate(bTime)) strFilter.Append("and TestEndTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) strFilter.Append("and TestEndTime <='" + eTime + "'");
                        break;

                }
            }
            strFilter.Append(" Order by DeptName asc, EmpCode asc");
            return SQLHelper.ExecuteSqlGetDrToDt(strFilter.ToString());
        }


        /// <summary>
        /// 后来增加的界面显示多个内容  多条件组合，查询一个员工的信息
        /// </summary>
        /// <param name="empCode"></param>
        /// <param name="empName"></param>
        /// <param name="empDept"></param>
        /// <param name="empDuty"></param>
        /// <param name="empArea"></param>
        /// <returns></returns>
        public DataTable GetOneInfo(string empCode, string empName, string empDept, string empDuty, string empArea)
        {
            StringBuilder strSQL = new StringBuilder("select top 1 * from View_Emp where 1=1 ");
            if (!string.IsNullOrWhiteSpace(empCode)) strSQL.Append(" and EmpCode='" + empCode + "'");
            if (!string.IsNullOrWhiteSpace(empName)) strSQL.Append(" and EmpName = '" + empName + "'");

            if (!string.IsNullOrWhiteSpace(empDept)) strSQL.Append(" and DeptName like '%" + empDept + "%'");
            if (!string.IsNullOrWhiteSpace(empDuty)) strSQL.Append(" and DutyName like '%" + empDuty + "%'");
            if (!string.IsNullOrWhiteSpace(empArea)) strSQL.Append(" and AreaName like '%" + empArea + "%'");

            return SQLHelper.ExecuteSqlGetDrToDt(strSQL.ToString());
        }



        /// <summary>
        /// 删除一个员工的信息
        /// </summary>
        /// <param name="empCode"></param>
        /// <returns></returns>
        public bool DeleteOneInfo(string empCode)
        {
            return SQLHelper.ExecuteSqlWithTransaction("Delete from Admin_Employee Where EmpCode='" + empCode + "' and EmpCode not in (Select EmpCode from Admin_Emp_Leave) and EmpCode not in (Select EmpCode from Admin_Emp_Passed)");
        }


        //上岗通知书
        public DataTable GetLists(string serWord, string deptID, string bTime, string eTime, string timeType)
        {
            //本月 入职并离职 也要显示
            StringBuilder strSQL = new StringBuilder("Select [DeptName], [EmpCode], [EmpName],[EmpSex], [DutyName],   [JoinTime],[StateName],[Memo]  From [View_Emp] Where (StateName not in('离职','请长假') Or DateDiff(month,JoinTime,LeaveTime)=0 ) ");
            if (!string.IsNullOrWhiteSpace(serWord))
                strSQL.Append(" And (EmpCode='" + serWord + "' Or EmpName Like '%" + serWord + "%')");
            else
            {

                if (deptID != "0" && !string.IsNullOrWhiteSpace(deptID))
                    strSQL.Append(" And DeptID=" + deptID);
                if (string.IsNullOrWhiteSpace(bTime) && string.IsNullOrWhiteSpace(eTime))
                {
                    strSQL.Append(" And DateDiff(month,JoinTime,getdate())=0");
                }
                else
                {
                    switch (timeType)
                    {
                        case "JoinTime":
                            if (RegexRule.IsDate(bTime))
                                strSQL.Append(" And JoinTime>='" + bTime + "'");
                            if (RegexRule.IsDate(eTime))
                                strSQL.Append(" And JoinTime<='" + eTime + "'");
                            break;
                        case "InputTime":
                            if (RegexRule.IsDate(bTime))
                                strSQL.Append(" And InputTime>='" + bTime + "'");
                            if (RegexRule.IsDate(eTime))
                                strSQL.Append(" And InputTime<='" + eTime + "'");
                            break;

                        case "LeaveTime":
                            if (RegexRule.IsDate(bTime))
                                strSQL.Append(" And LeaveTime>='" + bTime + "'");
                            if (RegexRule.IsDate(eTime))
                                strSQL.Append(" And LeaveTime<='" + eTime + "'");
                            break;
                    }
                }

            }
            strSQL.Append(" Order by DeptID");
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt(strSQL.ToString());
            return dt;
        }

        //入职汇总
        public DataTable GetLists(string bTime, string eTime)
        {
            string strSQL = "Select [DeptName], [EmpCode], [EmpName],[EmpSex], [DutyName],[SecurityAmount], [JoinTime],[Memo]  From [View_Emp] Where DutyName<>'售货员' and StateName not in('离职','请长假') ";
            if (!string.IsNullOrWhiteSpace(bTime) && RegexRule.IsDate(bTime))
                strSQL += " And JoinTime>='" + bTime + "'";
            if (!string.IsNullOrWhiteSpace(eTime) && RegexRule.IsDate(eTime))
                strSQL += " And JoinTime<='" + eTime + "'";
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt(strSQL);

            //if (dataSet.Tables[0].Rows.Count <= 0)
            //{
            //    for (int i = 0; i < 5; i++)
            //    {
            //        DataRow dr = dataSet.Tables[0].NewRow();
            //        dataSet.Tables[0].Rows.Add(dr);
            //    }
            //}
            return dt;
        }

        //选择5位员工编号
        public string CreateEmpCode(string strHead)
        {
            string strSQL = string.Empty;


            int len = strHead.Length;
            if (strHead == "")//自动编号
            { }
            else if (len == 1)
            {
                strSQL = "select max (substring(EmpCode,2,4)) as MaxNum from Admin_Employee where EmpCode like '" + strHead + "%' and len(EmpCode)=5";
                // strFoot = Getfooter(strSQL, len);
            }
            else if (len == 2)
            {
                strSQL = "select max (substring(EmpCode,3,3)) as MaxNum from Admin_Employee where EmpCode like '" + strHead + "%' and len(EmpCode)=5";
            }
            string strFoot = Getfooter(strSQL, len);
            return strHead + strFoot;
        }
        public DataTable GetCodeList(string deptID, string strHead)
        {
            if (deptID == "0")
                return SQLHelper.ExecuteSqlGetDataSet("select top 200 empcode,EmpName,statu from admin_empcode where (empcode like '" + strHead + "%'  OR empName like '%" + strHead + "%')  and  empcode not in (select empcode from admin_employee) order by empcode asc").Tables[0];
            else
                return SQLHelper.ExecuteSqlGetDataSet("select top 200 empcode,EmpName,statu from admin_empcode where DeptID=" + deptID + " and  (empcode like '" + strHead + "%' OR empName like '%" + strHead + "%') and  empcode not in (select empcode from admin_employee)  order by empcode asc").Tables[0];
        }
        //生成工号字典
        public bool CreateCodeDict(string len)
        {
            SqlParameter para0 = new SqlParameter("@len", SqlDbType.Int);
            para0.Value = len;
            SqlParameter[] spList = { para0 };
            return SQLHelper.ExecutePorcedure("sp_JimCreateEmpCode", spList);
        }

        //设置工号状态
        public bool SetCodeStatu(string strCode, string deptID, string empName, string statu)
        {
            string strSQL = "Update Admin_EmpCode Set EmpName='" + empName + "', DeptID=" + deptID + ", Statu='" + statu + "' Where EmpCode='" + strCode + "'";
            return SQLHelper.ExecuteSql(strSQL);
        }
        public DataTable GetCodeStatu(string strCode)
        {
            return SQLHelper.ExecuteSqlGetDataSet("select * from Admin_EmpCode  Where EmpCode='" + strCode + "'").Tables[0];
        }

        private string Getfooter(string strSQL, int length)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt(strSQL);
            string result = string.Empty;
            if (dt == null)
                result = "0001";
            else
            {
                result = dt.Rows[0]["MaxNum"].ToString();
                if (string.IsNullOrWhiteSpace(result)) result = "0001";
                else
                {
                    if (RegexRule.IsInt(result))
                    {
                        int code = Int32.Parse(result);
                        code++;
                        result = code.ToString();
                        int len = result.Length;
                        if (length == 1)
                        {
                            if (len == 1) result = "000" + result;
                            if (len == 2) result = "00" + result;
                            if (len == 3) result = "0" + result;
                        }
                        else if (length == 2)
                        {
                            if (len == 1) result = "00" + result;
                            if (len == 2) result = "0" + result;
                        }
                    }
                    //或则不是数字
                }
            }
            return result;
        }
        #endregion

        #region 统计汇总

        //根据部门统计
        public DataTable GetListByDept(string stateID, string dutyID)
        {
            string strSQL = "select DeptName as 部门名称,count(*) as 人数 from view_emp where 1=1 ";
            if (stateID == "10000")
            {
                strSQL += "  and StateID<>3 ";
            }
            else if (stateID != "" && stateID != "0")
                strSQL += "  and StateID=" + stateID;
            if (dutyID != "" && dutyID != "0")
                strSQL += "  and DutyID=" + dutyID;
            strSQL += " group by DeptName order by 人数 DESC";
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }
        //根据部门 统计在职员工 （包括正式的和试用的）
        public DataTable GetListActiveEmp()
        {
            string strSQL = "select DeptName as 部门名称,count(*) as 人数 from view_emp where StateID in (1,2) ";
            strSQL += " group by DeptName order by 人数 DESC";
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }
        //按照学历统计
        public DataTable GetListByEdu(string stateID, string dutyID)
        {
            string strSQL = "select EducationName as 学历,count(*) as 人数 from view_emp where 1=1 ";
            if (stateID == "10000")
            {
                strSQL += "  and StateID<>3 ";
            }
            else if (stateID != "" && stateID != "0")
            {
                strSQL += "  and StateID=" + stateID;
            }
            if (dutyID != "" && dutyID != "0")
            {
                strSQL += " and  DutyID=" + dutyID;
            }
            strSQL += " group by EducationName  order by 人数 DESC";
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }
        //根据职位统计
        public DataTable GetListByDuty(string stateID, string deptID)
        {
            string strSQL = "select DutyName as 职位,count(*) as 人数 from view_emp where 1=1 ";
            if (stateID == "10000")
            {
                strSQL += "  and StateID<>3 ";
            }
            else if (stateID != "" && stateID != "0")
            {
                strSQL += " and  StateID=" + stateID;
            }
            if (deptID != "" && deptID != "0")
            {
                strSQL += "  and DeptID=" + deptID;
            }
            strSQL += " group by DutyName  order by 人数 DESC";
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }
        /// <summary>
        /// 根据区域显示 
        /// </summary>
        /// <param name="empCode">员工编号</param>
        /// <param name="deptID">部门ID</param>
        /// <param name="areaID">区域ID</param>
        /// <returns>列表</returns>
        public DataTable GetListByArea(string empCode, string deptID, string areaID)
        {
            StringBuilder strSQL = new StringBuilder("select " + strFields + " from view_emp where  StateID<>3 and  AreaID=" + areaID);
            if (!string.IsNullOrWhiteSpace(empCode))
            {
                strSQL.Append(" and (EmpCode='" + empCode + "' OR EmpName like '%" + empCode + "%') ");
            }
            if (deptID != "" && deptID != "0")
            {
                strSQL.Append(" and  DeptID=" + deptID);
            }

            strSQL.Append("   order by DeptID ASC, EmpCode asc ");
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL.ToString());
        }

        //根据区域统计
        public DataTable GetListByArea(string stateID, string deptID)
        {
            string strSQL = "select AreaName as 区域,count(*) as 人数 from view_emp where 1=1";

            if (stateID == "10000")
            {
                strSQL += "  and StateID<>3 ";
            }
            else if (stateID != "" && stateID != "0")
            {
                strSQL += " and StateID=" + stateID;
            }
            if (deptID != "" && deptID != "0")
            {
                strSQL += " and  DeptID=" + deptID;
            }

            strSQL += " group by AreaName  order by 人数 DESC";
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }
        /// <summary>
        ///对在上面4种统计界面，弹出窗口显示统计的明细进行导出 (显示和导出共同使用)
        /// </summary>
        /// <param name="gvCaption">是那一种，根据gvList的标题区分</param>
        /// <param name="searchParam">点击的分类：部门，学习，职位，区域</param>
        /// <param name="stateID">查询的4个参数(下面3个)</param>
        /// <param name="dutyID"></param>
        /// <param name="stateID0"></param>
        /// <param name="deptID"></param>
        /// <param name="flag">true 导出所有分类 false 导出当前分类</param>
        /// <returns>ds</returns>
        public DataTable GetStaticDetail(string gvCaption, string searchParam, string stateID, string dutyID, string stateID0, string deptID, bool flag, string getType)
        {
            StringBuilder strSQL = new StringBuilder("Select ");
            if (getType == "Show")
                strSQL.Append(strFields);
            else if (getType == "Export")
                strSQL.Append(empFields);
            else
                strSQL.Append(allFields);

            strSQL.Append(" From [View_Emp] ");
            // [DeptName], [EmpCode], [EmpName], [DutyName], [AreaName], [StateName], [EmpSex], [EducationName], [Registry], [JoinTime], [SafetyAmount], [SalaryState], [Remark], [WorkGroup], [Mobile]
            switch (gvCaption)
            {

                case "部门"://从雇员表中取出 该部门的所有员工
                    if (flag)
                        strSQL.Append(" Where 1=1 ");
                    else
                        strSQL.Append(" Where [DeptName]='" + searchParam + "'");
                    if (stateID == "10000")
                    {
                        strSQL.Append("  and StateID<>3 ");
                    }
                    else if (stateID != "" && stateID != "0") strSQL.Append("  and StateID=" + stateID);
                    if (dutyID != "" && dutyID != "0") strSQL.Append("  and DutyID=" + dutyID);
                    break;
                case "学历":
                    if (flag)
                        strSQL.Append(" Where 1=1 ");
                    else
                        strSQL.Append(" Where [EducationName]='" + searchParam + "'");
                    if (stateID == "10000")
                    {
                        strSQL.Append("  and StateID<>3 ");
                    }
                    else if (stateID != "" && stateID != "0") strSQL.Append("  and StateID=" + stateID);
                    if (dutyID != "" && dutyID != "0") strSQL.Append("  and DutyID=" + dutyID);
                    break;
                case "职位":
                    if (flag)
                        strSQL.Append(" Where 1=1 ");
                    else
                        strSQL.Append(" Where [DutyName]='" + searchParam + "'");
                    if (stateID == "10000")
                    {
                        strSQL.Append("  and StateID<>3 ");
                    }
                    else if (stateID0 != "" && stateID0 != "0") strSQL.Append("  and  StateID=" + stateID0);
                    if (deptID != "" && deptID != "0") strSQL.Append(" and DeptID=" + deptID);

                    break;
                case "区域":
                    if (flag)
                        strSQL.Append(" Where 1=1 ");
                    else
                        strSQL.Append(" Where [AreaName]='" + searchParam + "'");
                    if (stateID == "10000")
                    {
                        strSQL.Append("  and StateID<>3 ");
                    }
                    else if (stateID0 != "" && stateID0 != "0") strSQL.Append("  and  StateID=" + stateID0);
                    if (deptID != "" && deptID != "0") strSQL.Append(" and DeptID=" + deptID);
                    break;
                default: //case "在职":
                    if (flag)
                        strSQL.Append(" Where 1=1 ");
                    else
                        strSQL.Append(" Where [DeptName]='" + searchParam + "'");
                    strSQL.Append("  and  StateID in (1,2)");
                    break;
            }
            strSQL.Append(" Order By DeptName ASC,EmpCode Asc");
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL.ToString());
        }
        //获取人员列表，带有下限
        public DataSet GetLimitList(string theYear, string theMonth)
        {
            return SQLHelper.ExecuteSqlGetDataSet("exec sp_JimLimitStatu " + theYear + "," + theMonth);
        }
        /// <summary>
        /// 获取某月入职人数列表 (即使是当月又离职了，也包含在内)
        /// </summary>
        /// <param name="iYear">年</param>
        /// <param name="iMonth">月</param>
        /// <param name="getType">是显示(View)还收导出(Export)</param>
        /// <returns>返回ds</returns>
        public DataTable GetMonthJoinList(int iYear, int iMonth, string getType)
        {
            if (getType == "Show")
                return SQLHelper.ExecuteSqlGetDrToDt("select DeptName,EmpCode,EmpName,DutyName ,StateName,JoinTime,CONVERT(varchar(10), LeaveTime, 23) as LeaveTime,Memo from Wage_SalaryListEnd where salyear =" + iYear + " and salmonth=" + iMonth + " order by DeptName ASC,EmpCode ASC");
            else if (getType == "Export")
                return SQLHelper.ExecuteSqlGetDrToDt("select DeptName,EmpCode,EmpName,DutyName ,StateName,JoinTime,CONVERT(varchar(10), LeaveTime, 23) as LeaveTime,Memo from Wage_SalaryListEnd where salyear =" + iYear + " and salmonth=" + iMonth + " order by  DeptName ASC,EmpCode ASC");
            else //if(getType == "All")
                return SQLHelper.ExecuteSqlGetDrToDt("select DeptName,EmpCode,EmpName,DutyName ,StateName,JoinTime,CONVERT(varchar(10), LeaveTime, 23) as LeaveTime,Memo from Wage_SalaryListEnd where salyear =" + iYear + " and salmonth=" + iMonth + " order by  DeptName ASC,EmpCode ASC");


        }
        /// <summary>
        /// 获取某年某月离职人员的列表 
        /// </summary>
        /// <param name="iYear"></param>
        /// <param name="iMonth"></param>
        /// <param name="getType"></param>
        /// <returns></returns>
        public DataTable GetMonthLeaveList(int iYear, int iMonth, string getType)
        {
            if (getType == "Show")
                return SQLHelper.ExecuteSqlGetDrToDt("select  DeptName,EmpCode,EmpName,DutyName ,StateName,JoinTime,CONVERT(varchar(10), LeaveTime, 23) as LeaveTime,Memo from View_Leave where DATEPART(year,LeaveTime ) =" + iYear + " and DATEPART(month,LeaveTime )=" + iMonth + " and LeaveType<>'请长假' order by  DeptName ASC,EmpCode ASC");
            else if (getType == "Export")
                return SQLHelper.ExecuteSqlGetDrToDt("select  DeptName,EmpCode,EmpName,DutyName ,StateName,JoinTime,CONVERT(varchar(10), LeaveTime, 23) as LeaveTime,Memo  from View_Leave where DATEPART(year,LeaveTime ) =" + iYear + " and DATEPART(month,LeaveTime )=" + iMonth + " and LeaveType<>'请长假' order by  DeptName ASC,EmpCode ASC");
            else
                return SQLHelper.ExecuteSqlGetDrToDt("select  DeptName,EmpCode,EmpName,DutyName ,StateName,JoinTime,CONVERT(varchar(10), LeaveTime, 23) as LeaveTime,Memo from View_Leave where DATEPART(year,LeaveTime ) =" + iYear + " and DATEPART(month,LeaveTime )=" + iMonth + " and LeaveType<>'请长假' order by  DeptName ASC,EmpCode ASC");

        }

        /// <summary>
        /// 获取某月人数的列表     某月人数   =  现有人数+某月以后离职人数-某月以后入职人数
        /// 直接从工资表统计
        /// </summary>
        /// <param name="iYear">年</param>
        /// <param name="iMonth">月</param>
        /// <param name="getType">输出类型</param>
        /// <returns></returns>
        public DataTable GetMonthNowList(int iYear, int iMonth, string getType)
        {

            //string theDATE = DateTime.Parse(iYear.ToString() + "-" + iMonth.ToString() + "-1").AddMonths(1).ToString("yyyy-MM-dd");
           // string theDATE2 = DateTime.Parse(iYear.ToString() + "-" + iMonth.ToString() + "-1").AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
            StringBuilder strSQL = new StringBuilder("select ");
              if (getType == "Show") strSQL.Append(" DeptName,EmpCode,EmpName,DutyName ,StateName,JoinTime,CONVERT(varchar(10), LeaveTime, 23) as LeaveTime,Memo  ");
            else if (getType == "Export") strSQL.Append(" DeptName,EmpCode,EmpName,DutyName ,StateName,JoinTime,CONVERT(varchar(10), LeaveTime, 23) as LeaveTime,Memo  ");
            else strSQL.Append(allFields);

            strSQL.Append(" from Wage_SalaryListEnd where ");
            strSQL.Append(" SalYear=" + iYear + " and salMonth=" + iMonth);
            strSQL.Append(" Order by DeptName asc,EmpCode asc ");
            return SQLHelper.ExecuteSqlGetDataSet(strSQL.ToString()).Tables[0];
        }
        /// <summary>
        /// 获取 统计数据输出(不是人员列表)
        /// </summary>
        /// <param name="iYear"></param>
        /// <param name="iMonth"></param>
        /// <returns></returns>
        public DataSet GetMonthNowNum(int iYear, int iMonth)
        {
            //  string strSQL = "Exec sp_JimMonthStatu " + iYear + "," + iMonth;
            // return SQLHelper.ExecuteSqlGetDataSet(strSQL);
            SqlParameter para0 = new SqlParameter("@paramYear", SqlDbType.Int);
            SqlParameter para1 = new SqlParameter("@ParamMonth", SqlDbType.Int);
            para0.Value = iYear;
            para1.Value = iMonth;
            SqlParameter[] spList = { para0, para1 };
            return SQLHelper.ExecutePorcedureGetDataSet("sp_JimMonthStatu", spList);

        }
        #endregion
        //获取最近2月入职，没有工资账号的员工信息列表
        public DataTable GetNoBankAccountList()
        {
            string strSQL = "select EmpCode,DeptName,EmpName,DutyName,EmpSex,SalaryState,SalaryAccount  from View_Emp where  isnull(SalaryAccount,'')='' and  (DATEDIFF(month, dbo.View_Emp.JoinTime, GETDATE()) <= 2) Order by DeptName ASC,EmpCode ASC";
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }
        /// <summary>
        /// 更改保险信息
        /// </summary>
        /// <param name="empCode"></param>
        /// <param name="empPay"></param>
        /// <returns></returns>
        public bool  UpdateInsuranceInfo(string empCode, string empPay)
        {
            return SQLHelper.ExecuteSql("update Admin_Employee set SafetyAmount=" + empPay + " where EmpCode='" + empCode + "'");
           
        }
        /// <summary>
        /// 判断一个员工的状态 （离职，试用，正式 等等)
        /// </summary>
        /// <param name="empCode">工号</param>
        /// /// <param name="stateID">状态（字典中不能更改）</param>
        /// <returns></returns>
        public bool IsState(string empCode, int stateID)
        {
            string count = SQLHelper.ExecuteSqlGetNumber("Select count(*) from Admin_Employee where StateID=" + stateID + " and  EmpCode =' " + empCode + "'");
            if (count == "1")
                return true;
            else
                return false;
        }
    }
}
